package org.anyline.simple.ds;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.proxy.jdbc.NClobProxyImpl;
import com.zaxxer.hikari.HikariDataSource;
import org.anyline.adapter.DataReader;
import org.anyline.data.adapter.DataReaderFactory;
import org.anyline.data.datasource.DataSourceHolder;
import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.data.runtime.RuntimeHolder;
import org.anyline.entity.Compare;
import org.anyline.entity.DataRow;
import org.anyline.entity.DataSet;
import org.anyline.environment.spring.data.jdbc.datasource.SpringJDBCDataSourceHolder;
import org.anyline.metadata.Column;
import org.anyline.metadata.Schema;
import org.anyline.metadata.Table;
import org.anyline.metadata.type.DatabaseType;
import org.anyline.proxy.ServiceProxy;
import org.anyline.service.AnylineService;
import org.anyline.util.BasicUtil;
import org.anyline.util.ConfigTable;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@SpringBootTest
public class DSTest {

    @Test
    public void num() throws Exception{
        ConfigTable.IS_SQL_DELIMITER_OPEN = true;
        Table table = new Table("test_"+System.currentTimeMillis());
        table.setComment("comment");
        table.addColumn("ID", "INT");
        table.addColumn("a1", "int");
        ServiceProxy.ddl().create(table);
        table = ServiceProxy.metadata().table(table.getName());
        System.out.println(table.getComment());
    }
    @Test
    public void empty(){
        AnylineService service = ServiceProxy.service();

        //service.query("crm_user", "+id:null"); // ID IS NULL
        //service.query("crm_user", "+id:");     // ID IS NULL
        //以上两种情况 经常是通过变量拼接出来如 "+id:"+value

        //ConfigStore.and方法中提供了EMPTY_VALUE_SWITCH用来决定空值(null和'')时的处理方式,默认IGNORE
        //如果已经明确了空值的情况可以直接写 configs.and("ID IS NULL").and("CODE = ''")
        /*
        IGNORE     //忽略当前条件  其他条件继续执行
       , BREAK	   //中断执行 整个命令不执行
       , NULL	   //生成 WHERE ID IS NULL
       , SRC	   //原样处理 会生成 WHERE ID = NULL
       , NONE	   //根据条件判断 ++或+
       */
        ConfigStore configs = new DefaultConfigStore();

        //默认忽略空值
        configs.and("I0", null);               // 忽略
        configs.and("I1", "");                   // 忽略
        configs.and("I2", "".split(","));        // 忽略
        configs.and("I3", new ArrayList<>());           // 忽略
        configs.and("I4", new String[]{});              // 忽略

        // +表示必须条件 无论什么值 添加到查询条件中
        configs.and("+K0", null);               // K0 IS NULL
        configs.and("+K1", "");                   // K1 IS NULL
        configs.and("+K2", "".split(","));        // K2 IS NULL
        configs.and("+K3", new ArrayList<>());           // K3 IS NULL
        configs.and("+K4", new String[]{});              // K4 IS NULL

        //EMPTY_VALUE_SWITCH.NULL 与 +key 等效
        configs.and(Compare.EMPTY_VALUE_SWITCH.NULL, "N0", null);               // N1 IS NULL
        configs.and(Compare.EMPTY_VALUE_SWITCH.NULL, "N1", "");                   // N2 IS NULL
        configs.and(Compare.EMPTY_VALUE_SWITCH.NULL, "N2", "".split(","));        // N3 IS NULL
        configs.and(Compare.EMPTY_VALUE_SWITCH.NULL, "N3", new ArrayList<>());           // N4 IS NULL
        configs.and(Compare.EMPTY_VALUE_SWITCH.NULL, "N4", new String[]{});              // N5 IS NULL


        // EMPTY_VALUE_SWITCH.SRC 与EMPTY_VALUE_SWITCH.NULL的区别
        // EMPTY_VALUE_SWITCH.NULL会把空值转换成null,
        // EMPTY_VALUE_SWITCH.SRC会把空值原样保留
        configs.and(Compare.EMPTY_VALUE_SWITCH.SRC, "S0", null);               // S0 IS NULL
        configs.and(Compare.EMPTY_VALUE_SWITCH.SRC, "S1", "");                   // S1 = ''
        configs.and(Compare.EMPTY_VALUE_SWITCH.SRC, "S2", "".split(","));        // S2 = ''
        configs.and(Compare.EMPTY_VALUE_SWITCH.SRC, "S3", new ArrayList<>());           // S3 IS NULL
        configs.and(Compare.EMPTY_VALUE_SWITCH.SRC, "S4", new String[]{});              // S4 SI NULL


        // ++ 表示 必须条件 并且 必须有值，如果没有值会中断整个SQL执行 EMPTY_VALUE_SWITCH.BREAK等效


        DataSet set = service.querys("crm_user(ID)", configs);
    }
    @Test
    public void mysql2pg() throws Exception{
        //从mysql复制结构+数据到pg
        AnylineService mysql = ServiceProxy.service(); //这里的数据源key对应配置文件中的 anyline.datasource-list=crm,erp,sso,mg,pg,doris,dm
        AnylineService pg = ServiceProxy.service("pg");
        init(mysql);
        LinkedHashMap<String, Table> tables = mysql.metadata().tables();//查询mysql全部表名
        for(Table mysql_table:tables.values()){
            String name = mysql_table.getName();
            Table pg_table = pg.metadata().table(name);
            if(null != pg_table){
                pg.ddl().drop(pg_table);
            }
            Table table = mysql.metadata().table(name);
            table.setSchema("PUBLIC");
            //mysql表复制到pg中
            table.setEngine(null);
            pg.ddl().create(table);
            //复制数据
            Long max = 0L;
            //检测最后一条数据
            DataRow last = pg.query(table, "ORDER BY ID DESC");
            if(null != last){
                max = last.getLong("ID", 0);
            }
            while (true){
                //从mysql中查出数据 这里不要用DataSet 太慢
                List<Map> list = mysql.maps(name,0,999, "ID>"+max);
                if(list.isEmpty()){
                    break;
                }
                //插入到pg一般需要修改一下默认日志(不显示日志)要不然日志太多
                //override表示重复数据不覆盖(忽略|跳过)
                ConfigStore configs = new DefaultConfigStore().IS_LOG_SQL(false).IS_LOG_SQL_PARAM(false).override(false);
                pg.insert(table, list, configs );
                max = BasicUtil.parseLong(list.get(list.size()-1).get("ID"), 0l);
            }
        }
    }
    @Test
    public void pg2mysql() throws Exception{
        AnylineService mysql = ServiceProxy.service(); //这里的数据源key对应配置文件中的 anyline.datasource-list=crm,erp,sso,mg,pg,doris,dm
        AnylineService pg = ServiceProxy.service("pg");
        //init(pg);
        Table query = new Table();
        query.setSchema("public");
        LinkedHashMap<String, Table> tables = pg.metadata().tables(query);//查询mysql全部表名
        for(Table pg_table:tables.values()){
            String name = pg_table.getName();
            Table mysql_table = mysql.metadata().table(name);
            if(null != mysql_table){
                mysql.ddl().drop(mysql_table);
            }
            Table table = pg.metadata().table(name);
            table.setCatalog("");
            table.setSchema("");
            mysql.ddl().create(table);
        }
    }
    public void init(AnylineService service) throws Exception {
        //初始化数据源 创建几个测试表
        for(int i=0; i<10; i++){
            String name = "origin_table_"+i;
            Table table = service.metadata().table(name);
            if(null != table){
                service.ddl().drop(table);
            }
            table = new Table(name);
            table.addColumn("id", "bigint").setPrimary(true).setAutoIncrement(true).setComment("主键");
            table.addColumn("code","varchar(10)").setComment("编号");
            table.addColumn("name","varchar(10)").setComment("名称");
            service.ddl().create(table);
            DataSet set = new DataSet();
            for(int j=0; j<10; j++){
                DataRow row = set.add();
                row.put("code", "code_"+i+"_"+j);
                row.put("name", "name_"+i+"_"+j);
            }
            service.insert(table, set);
        }
    }

    @Test
    public void copyDatasource() throws Exception{
        List<String> list = DataSourceHolder.copy("default");
        System.out.println(list);
        list = DataSourceHolder.copy("default");
        System.out.println(list);
    }
    @Test
    public void dm2doris() throws Exception{
        AnylineService dm = ServiceProxy.service("dm");
        AnylineService doris = ServiceProxy.service("doris");
        Table table = dm.metadata().table("tab_number", false);
        if(null != table){
            dm.ddl().drop(table);
        }
        table = new Table("tab_number");
        table.addColumn("id", "int").setPrimary(true);
        table.addColumn("qty", "number(10,2)");
        dm.ddl().create(table);
        dm.metadata().table("tab_number", false);
        doris.ddl().create(table);
    }

    @Test
    public  void pg() throws Exception {
        DruidDataSource ds3 = new DruidDataSource();
        ds3.setUrl("jdbc:postgresql://localhost:35432/simple");
        ds3.setDriverClassName("org.postgresql.Driver");
        ds3.setUsername("postgres");
        ds3.setPassword("Docker_postgres_5432");
        DataSourceHolder.reg("pgs", ds3);
        DataSet set = ServiceProxy.service("pgs").querys("SELECT *FROM pg_catalog.pg_namespace");
        System.out.println(set);
        Schema schema = ServiceProxy.service("pgs").metadata().schema();
        System.out.println(schema);
        Table table = new Table("simple","public","tb_"+System.currentTimeMillis());
        table.addColumn("ID","INT");
        ServiceProxy.service("pgs").ddl().create(table);

    }
    @Test
    public  void druid2() throws Exception {
        ConfigTable.IS_LOG_ADAPTER_MATCH = true;
        String key = "d1";
        DruidDataSource ds2 = new DruidDataSource();
        ds2.setUrl("jdbc:mysql://localhost:33306/simple_sso?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true");
        ds2.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds2.setUsername("root");
        ds2.setPassword("root");
        DataSourceHolder.reg(key, ds2);
        System.out.println("注册数据库成功:" + ds2);
        DatabaseType type = DataSourceHolder.dialect(key);
        type = ServiceProxy.service(key).metadata().type();


        key = "d2";
        DruidDataSource ds3 = new DruidDataSource();
        ds3.setUrl("jdbc:mysql://localhost:9030/simple?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&adapter=doris");
        ds3.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds3.setUsername("root");
        DataSourceHolder.reg(key, ds3);
        System.out.println("注册数据库成功:" + ds3);
        ServiceProxy.service("d2").tables();
    }

    @Test
    public void hive() throws Exception {
        String driver = "org.apache.hive.jdbc.HiveDriver";
        String url = "jdbc:hive2://localhost:31001/default?adapter=hive";
        String user = "root";
        String password = "root";
        DruidDataSource params = new DruidDataSource();
        params.setConnectionErrorRetryAttempts(3);
        params.setBreakAfterAcquireFailure(true);
        params.setUrl(url);
        if (driver != null && !"".equals(driver)) {
            params.setDriverClassName(driver);
        }
        params.setUsername(user);
        params.setPassword(password);
        try {
            DataSourceHolder.reg("aaa", params);
            AnylineService service1 = ServiceProxy.service("aaa");
            service1.metadata().tables();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    @Test
    public void maxCompute() throws Exception {
        String driver = "com.aliyun.odps.jdbc.OdpsDriver";
        String url = "jdbc:odps:http://service.cn-shanghai.maxcompute.aliyun.com/api?project=df_cs_537817&useProjectTimeZone=true;";
        String user = "**";
        String password = "**";
        DruidDataSource params = new DruidDataSource();
        params.setUrl(url);
        params.setDriverClassName(driver);
        params.setUsername(user);
        params.setPassword(password);
        try {
            DataSourceHolder.reg("mc", params);
            AnylineService service1 = ServiceProxy.service("mc");
            //service1.metadata().tables();
            service1.querys("jdbc_test");
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Test
    public void doris() throws Exception{
        //doris因为与mysql使用同样的协议并且接口返回标识一样，所以识别不出来，需要在url上指定adapter
        try {
            String url = "jdbc:mysql://localhost:33306/simple_sso?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
            DataSourceHolder.reg("sso", "com.zaxxer.hikari.HikariDataSource", "com.mysql.cj.jdbc.Driver", url, "root", "root");

            url = "jdbc:mysql://localhost:9030/simple?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&adapter=doris";
            DataSourceHolder.reg("ds", "com.zaxxer.hikari.HikariDataSource", "com.mysql.cj.jdbc.Driver", url, "root", "");
            ServiceProxy.service("ds").tables();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    @Test
    public void copyTable() throws Exception{
        //测试表
        Table table = ServiceProxy.metadata().table("CRM_USER");
        if(null != table){
            ServiceProxy.ddl().drop(table);
        }
        table = new Table("CRM_USER");
        Column column = new Column("ID").autoIncrement(true).setType("int").primary(true);
        table.addColumn(column);
        table.addColumn("CODE","varchar(10)").setComment("编号");
        table.addColumn("NAME","varchar(10)").setComment("名称");
        ServiceProxy.ddl().create(table);

        //从第一个库中取出表结构
        table = ServiceProxy.metadata().table("CRM_USER");

        Table chk = ServiceProxy.service("sso").metadata().table("CRM_COPY");
        if(null != chk){
            ServiceProxy.service("sso").ddl().drop(chk);
        }
        table.setName("CRM_COPY");
        //在第二个库中创建
        ServiceProxy.service("sso").ddl().create(table);
    }
    @Test
    public void con(){

        JdbcTemplate jdbc = (JdbcTemplate) RuntimeHolder.runtime("sso").getProcessor();
        DataSource ds = jdbc.getDataSource();
        Connection con = null;
        try {
            con = DataSourceUtils.getConnection(ds);
            DatabaseMetaData meta = con.getMetaData();
            String version = meta.getDatabaseProductVersion();
            System.out.println(version);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(null != con && !DataSourceUtils.isConnectionTransactional(con, ds)){
                DataSourceUtils.releaseConnection(con, ds);
            }
        }
    }
    @Test
    public void test() throws Exception{

        try {ConfigTable.IS_THROW_CONVERT_EXCEPTION = true;

            String url = "jdbc:mysql://localhost:33306/simple_sso?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
            DataSourceHolder.reg("sso", "com.zaxxer.hikari.HikariDataSource", "com.mysql.cj.jdbc.Driver", url, "root", "root");


            List<String> list = DataSourceHolder.copy("sso");
            System.out.println(list);
        }catch (Exception e){
            e.printStackTrace();
        }
        AnylineService service = ServiceProxy.service("sso");
        HikariDataSource ds = (HikariDataSource) SpringJDBCDataSourceHolder.datasource("sso");
        for(int i=0;i <1000; i++){
            Table table = ServiceProxy.metadata().table( "sso_user", true);
            System.out.println("active:"+ds.getHikariPoolMXBean().getActiveConnections());
        }
    }

    @Test
    public void druid(){
        try {
            String url = "jdbc:mysql://localhost:33306/simple_sso?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
            DataSourceHolder.reg("sso", "com.alibaba.druid.pool.DruidDataSource", "com.mysql.cj.jdbc.Driver", url, "root", "root");

            LinkedHashMap<String,Table> tables = ServiceProxy.service("sso").metadata().tables();
            for(String table:tables.keySet()){
                Table t = ServiceProxy.service("sso").metadata().table(true, table, true);
                System.out.println(t);
            }
            Long[] ids = new Long[]{1L,3L};
            //ServiceProxy.deletes("crm_user","id", ids);
            ServiceProxy.deletes("crm_user","id", ids);
            DataRow row = ServiceProxy.service("sso").query("sso_user");
            System.out.println(row);
        }catch (Exception e){
            e.printStackTrace();
        }

        DataReaderFactory.reg(new Object[]{NClobProxyImpl.class}, new DataReader() {
            @Override
            public Object read(Object value) {
                if(value instanceof NClobProxyImpl){
                    try {
                        NClobProxyImpl clob = (NClobProxyImpl) value;
                        long len = clob.length();
                        if (len > 0) {
                            return clob.getSubString(0, (int)len);
                        }
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }
                return value;
            }
        });
    }


    @Test
    public void work() throws Exception{
        System.out.println("123");
        String key = "hana";
        DruidDataSource ds2 = new DruidDataSource();
        ds2.setUrl("jdbc:mysql://127.0.0.1:33306/crm?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8");
        ds2.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds2.setUsername("root");
        ds2.setPassword("root");
        DataSourceHolder.reg(key, ds2);
        System.out.println(DataSourceHolder.list());

        AnylineService crmService = ServiceProxy.service("hana"); //返回crm数据源对应的service
        RuntimeHolder.destroy(key);
        System.out.println(DataSourceHolder.list());
        System.out.println("注册数据库成功:" + ds2);
    }
    /**
     * 临时数据源，用完后被GC自动回收，默认不支持事务
     */
    @Test
    public void temporary() throws Exception {
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        String url = "jdbc:mysql://localhost:33306/simple?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        DruidDataSource ds = new DruidDataSource();
        ds.setUrl(url);
        ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds.setUsername("root");
        ds.setPassword("root");
        ds.setConnectionErrorRetryAttempts(3);
        ds.setBreakAfterAcquireFailure(true);

        ds.setMaxWait(30000);
        AnylineService service = ServiceProxy.temporary(ds);
        System.out.println("validate:"+ServiceProxy.service().validity());
    }

    }
